package org.mobile.logic;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import org.mobile.beans.Order;
import org.mobile.beans.Phone;
import org.mobile.database.connection.ConnectionManager;
import org.mobile.database.statements.Statements;

public class OrderLogic {
	private static OrderLogic instance;
	
	private OrderLogic(){
		
	}
	
	public static OrderLogic getInstance(){
		if(instance == null){
			instance= new OrderLogic();
		}
		return instance;
	}
	
	public List<Order> getOrders(int userID){
		Connection conn= null;
		PreparedStatement st= null;
		ResultSet rs= null;
		ArrayList<Order> orders= new ArrayList<Order>();
		conn= ConnectionManager.getManager().getConnection();
		try {
			st= conn.prepareStatement(Statements.SELECT_ORDER);
			st.setInt(1, userID);
			rs= st.executeQuery();
			while(rs.next()){
				Order order= new Order();
				int orderID= rs.getInt("orderID");
				order.setId(orderID);
				order.setDate(rs.getDate("date"));
				order.setStatus(rs.getString("status"));
				order.setPhones((ArrayList<Phone>) PhoneLogic.getInstance().getPhones(orderID));
				orders.add(order);
			}
			return orders;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		finally{
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public boolean removeOrder(int orderID){
		Connection conn= null;
		PreparedStatement st= null;
		
		conn= ConnectionManager.getManager().getConnection();
		try {
			st= conn.prepareStatement(Statements.REMOVE_ORDER);
			st.setInt(1, orderID);
			st.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally{
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public boolean removeOrderByUserID(int userID){
		Connection conn= null;
		PreparedStatement st= null;
		
		conn= ConnectionManager.getManager().getConnection();
		try {
			st= conn.prepareStatement(Statements.REMOVE_ORDER_BY_USER_ID);
			st.setInt(1, userID);
			st.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally{
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public boolean addOrder(int userID, List<Phone> phoneList, String status){
		Connection conn= null;
		PreparedStatement st= null;
		Statement stm= null;
		ResultSet rs= null;
		
		conn= ConnectionManager.getManager().getConnection();
		try {
			conn.setAutoCommit(false);
			st= conn.prepareStatement(Statements.ADD_ORDER);
			st.setDate(1, new Date(new java.util.Date().getTime()));
			st.setString(2, status);
			st.executeUpdate();
			stm= conn.createStatement();
			rs= stm.executeQuery(Statements.MAX_ID);
			int orderID= -1;
			while(rs.next()){
				orderID= rs.getInt(1);
			}
			for(Phone p : phoneList ){
				st= conn.prepareStatement(Statements.ADD_ORDER_PHONE);
				st.setInt(1, orderID);
				st.setInt(2, p.getPhoneId());
				st.executeUpdate();
				
			}
			st= conn.prepareStatement(Statements.ADD_USER_ORDER);
			st.setInt(1, userID);
			st.setInt(2, orderID);
			st.executeUpdate();
			conn.commit();
			return true;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		}
		finally{
			try {
				conn.setAutoCommit(true);
				st.close();
				rs.close();
				stm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public boolean changeStatus(String status, int orderID){
		Connection conn= null;
		PreparedStatement st= null;
		
		conn= ConnectionManager.getManager().getConnection();
		try {
			st= conn.prepareStatement(Statements.UPDATE_ORDER_STATUS);
			st.setString(1, status);
			st.setInt(2, orderID);
			st.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally{
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public List<Order> getAllOrders(){
		Connection conn= null;
		Statement st= null;
		ResultSet rs= null;
		ArrayList<Order> orders= new ArrayList<Order>();
		conn= ConnectionManager.getManager().getConnection();
		try {
			st= conn.createStatement();
			rs= st.executeQuery(Statements.SELECT_ALL_ORDERS);
			while(rs.next()){
				Order order= new Order();
				int orderID= rs.getInt("orderID");
				order.setId(orderID);
				order.setDate(rs.getDate("date"));
				order.setStatus(rs.getString("status"));
				order.setPhones((ArrayList<Phone>) PhoneLogic.getInstance().getPhones(orderID));
				orders.add(order);
			}
			return orders;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		finally{
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
